-- 研发费用明细
alter proc proc_czly_ResearchFeeDetailRpt(
    @beginDt datetime,
    @endDt datetime,
    @orgNo varchar(55)
)as
begin
set nocount on

if isnull(@beginDt, '')='' set @beginDt=getdate()
if isnull(@endDt, '')='' set @endDt=getdate()

-- 获取数据
select fd.FF100005, fd.FFLEX9, FDEBIT-FCREDIT FAmt
into #t_data
from V_CN_VOUCHER v
inner join V_CN_VOUCHERENTRY ve on ve.FVOUCHERID=v.FVOUCHERID
inner join T_BD_ACCOUNT a on a.FACCTID=ve.FACCOUNTID
inner join T_BD_FLEXITEMDETAILV fd on fd.FID=ve.FDetailID
inner join T_BD_EXPENSE ep on ep.FEXPID=fd.FFLEX9
inner join T_ORG_ORGANIZATIONS org on org.FOrgId=v.FACCTORGID
where a.FNumber='6604' 
-- and v.FYear=year(@date) and v.FPeriod=month(@date)
and FDate between @beginDt and @endDt
and org.FNumber=@orgNo
and dbo.fun_czly_IsCarryForward(v.FVOUCHERID)=0

if @orgNo='SS' and @beginDt >= '2021-01-01' and @beginDt <= '2021-01-31' and @endDt >= @beginDt
begin
    -- 五官科管式、盖板式
    insert into #t_data values('60388888468daf', 20052, 59400)      -- 工资
    insert into #t_data values('60388888468daf', 253965, 15852.33)      -- 五险
    insert into #t_data values('60388888468daf', 253953, 5939)      -- 公积金
    insert into #t_data values('60388888468daf', 350832, 1188)      -- 工会经费
    insert into #t_data values('60388888468daf', 383919, 1200)      -- 津贴
    insert into #t_data values('60388888468daf', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('60388888468daf', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('60388888468daf', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('60388888468daf', 20142, 26.92)      -- 办公费
    insert into #t_data values('60388888468daf', 412298, 672.17)      -- 专利及软件费
    -- 基础外科手术器械增扩—剪类
    insert into #t_data values('60388897468dbb', 20052, 32330)       -- 工资
    insert into #t_data values('60388897468dbb', 253965, 8538.93)      -- 五险
    insert into #t_data values('60388897468dbb', 253953, 3024)      -- 公积金
    insert into #t_data values('60388897468dbb', 350832, 646.6)      -- 工会经费
    insert into #t_data values('60388897468dbb', 383919, 1600)      -- 津贴
    insert into #t_data values('60388897468dbb', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('60388897468dbb', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('60388897468dbb', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('60388897468dbb', 20142, 26.92)      -- 办公费
    insert into #t_data values('60388897468dbb', 412298, 672.17)      -- 专利及软件费
    -- 骨科器械增扩
    insert into #t_data values('603888a7468dc5', 20052, 26430)       -- 工资
    insert into #t_data values('603888a7468dc5', 253965, 6849.9)      -- 五险
    insert into #t_data values('603888a7468dc5', 253953, 2555)      -- 公积金
    insert into #t_data values('603888a7468dc5', 350832, 528.6)      -- 工会经费
    insert into #t_data values('603888a7468dc5', 383919, 1200)      -- 津贴
    insert into #t_data values('603888a7468dc5', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('603888a7468dc5', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('603888a7468dc5', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('603888a7468dc5', 20142, 26.92)      -- 办公费
    insert into #t_data values('603888a7468dc5', 412298, 672.17)      -- 专利及软件费
    -- 微创手术器械一期
    insert into #t_data values('603888ec468de9', 20052, 27300)       -- 工资
    insert into #t_data values('603888ec468de9', 253965, 7083.45)      -- 五险
    insert into #t_data values('603888ec468de9', 253953, 2245)      -- 公积金
    insert into #t_data values('603888ec468de9', 350832, 546)      -- 工会经费
    insert into #t_data values('603888ec468de9', 383919, 1200)      -- 津贴
    insert into #t_data values('603888ec468de9', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('603888ec468de9', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('603888ec468de9', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('603888ec468de9', 20142, 26.92)      -- 办公费
    insert into #t_data values('603888ec468de9', 412298, 672.17)      -- 专利及软件费
    -- 神经外科器械增扩
    insert into #t_data values('603888f7468df0', 20052, 65700)       -- 工资
    insert into #t_data values('603888f7468df0', 253965, 17862.92)      -- 五险
    insert into #t_data values('603888f7468df0', 253953, 6121)      -- 公积金
    insert into #t_data values('603888f7468df0', 350832, 1314)      -- 工会经费
    insert into #t_data values('603888f7468df0', 383919, 1600)      -- 津贴
    insert into #t_data values('603888f7468df0', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('603888f7468df0', 412294, 1457.53)      -- 器具产品
    insert into #t_data values('603888f7468df0', 240775, 23001.04)      -- 设备折旧费
    insert into #t_data values('603888f7468df0', 20142, 26.94)      -- 办公费
    insert into #t_data values('603888f7468df0', 412298, 672.17)      -- 专利及软件费
    -- 关节镜手术配套器械二期
    insert into #t_data values('60388900468df7', 20052, 48200)       -- 工资
    insert into #t_data values('60388900468df7', 253965, 12315.44)      -- 五险
    insert into #t_data values('60388900468df7', 253953, 4975)      -- 公积金
    insert into #t_data values('60388900468df7', 350832, 964)      -- 工会经费
    insert into #t_data values('60388900468df7', 383919, 1200)      -- 津贴
    insert into #t_data values('60388900468df7', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('60388900468df7', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('60388900468df7', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('60388900468df7', 20142, 26.92)      -- 办公费
    insert into #t_data values('60388900468df7', 412298, 672.17)      -- 专利及软件费
    -- 心胸血管外科器械增扩
    insert into #t_data values('6038890b468e06', 20052, 26830)       -- 工资
    insert into #t_data values('6038890b468e06', 253965, 7437.26)      -- 五险
    insert into #t_data values('6038890b468e06', 253953, 2613)      -- 公积金
    insert into #t_data values('6038890b468e06', 350832, 536.6)      -- 工会经费
    insert into #t_data values('6038890b468e06', 383919, 1200)      -- 津贴
    insert into #t_data values('6038890b468e06', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('6038890b468e06', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('6038890b468e06', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('6038890b468e06', 20142, 26.92)      -- 办公费
    insert into #t_data values('6038890b468e06', 412298, 672.17)      -- 专利及软件费
    -- 脊柱微创椎间孔镜手术器械
    insert into #t_data values('60388915468e08', 20052, 31700)       -- 工资
    insert into #t_data values('60388915468e08', 253965, 5679.28)      -- 五险
    insert into #t_data values('60388915468e08', 253953, 1852)      -- 公积金
    insert into #t_data values('60388915468e08', 350832, 398)      -- 工会经费
    insert into #t_data values('60388915468e08', 383919, 1600)      -- 津贴
    insert into #t_data values('60388915468e08', 250216, 58052.04)      -- 新品试制成品
    insert into #t_data values('60388915468e08', 412294, 1457.55)      -- 器具产品
    insert into #t_data values('60388915468e08', 240775, 23001.05)      -- 设备折旧费
    insert into #t_data values('60388915468e08', 20142, 26.92)      -- 办公费
    insert into #t_data values('60388915468e08', 412298, 672.17)      -- 专利及软件费
end

declare @t_cost_items table(
    fseq int,
    fid int,
    fname varchar(55)
)
insert into @t_cost_items select 0, FExpId, '工资' from T_BD_EXPENSE where F_PAEZ_ClsSeq=1
insert into @t_cost_items select 1, FExpId, '五险' from T_BD_EXPENSE where F_PAEZ_ClsSeq=2
insert into @t_cost_items select 2, FExpId, '公积金' from T_BD_EXPENSE where F_PAEZ_ClsSeq=4
insert into @t_cost_items values(3,  350832, '工会经费')
insert into @t_cost_items select 4, FExpId, '津贴' from T_BD_EXPENSE where F_PAEZ_ClsSeq=5
insert into @t_cost_items select 5, FExpId, '会议费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=23
insert into @t_cost_items values(6,  250216, '新品试制成品')
insert into @t_cost_items values(7,  412292, '测试注册费-产品检测费')
insert into @t_cost_items values(8,  412293, '测试注册费-质量检测费')
insert into @t_cost_items values(9,  412294, '测试注册费-器具产品')
insert into @t_cost_items values(10, 412295, '测试注册费-产品注册费')
insert into @t_cost_items values(11, 250218, '顾问鉴定费')
insert into @t_cost_items values(12, 250208, '样品费')
insert into @t_cost_items values(13, 240775, '设备折旧费') --研发费用折旧
insert into @t_cost_items select 14, FExpId, '差旅费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=9
insert into @t_cost_items select 15, FExpId, '办公费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=10
insert into @t_cost_items values(16, 250219, '认证费') --认证费
insert into @t_cost_items select 17, FExpId, '专利及软件费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=42
insert into @t_cost_items values(18, 412299, '临床试验费')
insert into @t_cost_items values(19, 412289, '产品推介费') --会议费-产品推介会议费
insert into @t_cost_items values(20, 412300, '质量调访费')
insert into @t_cost_items values(21, 251384, '水电费')
insert into @t_cost_items select 22, FExpId, '房租及物业费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=47
insert into @t_cost_items select 23, FExpId, '委外加工费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=65
insert into @t_cost_items select 24, FExpId, '长期待摊费用' from T_BD_EXPENSE where F_PAEZ_ClsSeq=48
insert into @t_cost_items select 25, FExpId, '市场活动费' from T_BD_EXPENSE where F_PAEZ_ClsSeq=26
insert into @t_cost_items select 26, FExpId, '其他' from T_BD_EXPENSE where F_PAEZ_ClsSeq=60

-- select * from @t_cost_items

SELECT ae.FENTRYID, ael.FDATAVALUE
into #t_research
FROM T_BAS_ASSISTANTDATA_L al
INNER JOIN T_BAS_ASSISTANTDATAENTRY ae ON ae.FID=al.FID
INNER JOIN T_BAS_ASSISTANTDATAENTRY_L ael ON ael.FENTRYID=ae.FENTRYID
WHERE al.FNAME='研发项目'

create table #t_result(
    FID int not null,
    FProjCostName varchar(55) not null,
    FItem0 decimal(23, 6) default(0),
    FItem1 decimal(23, 6) default(0),
    FItem2 decimal(23, 6) default(0),
    FItem3 decimal(23, 6) default(0),
    FItem4 decimal(23, 6) default(0),
    FItem5 decimal(23, 6) default(0),
    FItem6 decimal(23, 6) default(0),
    FItem7 decimal(23, 6) default(0),
    FItem8 decimal(23, 6) default(0),
    FItem9 decimal(23, 6) default(0),
    FItem10 decimal(23, 6) default(0),
    FItem11 decimal(23, 6) default(0),
    FItem12 decimal(23, 6) default(0),
    FItem13 decimal(23, 6) default(0),
    FItem14 decimal(23, 6) default(0),
    FItem15 decimal(23, 6) default(0),
    FItem16 decimal(23, 6) default(0),
    FItem17 decimal(23, 6) default(0),
    FItem18 decimal(23, 6) default(0),
    FItem19 decimal(23, 6) default(0),
    FItem20 decimal(23, 6) default(0),
    FItem21 decimal(23, 6) default(0),
    FItem22 decimal(23, 6) default(0),
    FItem23 decimal(23, 6) default(0),
    FItem24 decimal(23, 6) default(0),
    FItem25 decimal(23, 6) default(0),
    FItem26 decimal(23, 6) default(0),
    FTotal decimal(23, 6) default(0)
)


declare @research_id varchar(55)=''
    ,@research_name varchar(55)=''
    ,@row_seq int=0
    ,@col_seq int=0
    ,@col_max int = (select max(fseq) from @t_cost_items)
    ,@value decimal(23, 6)=0
    ,@row_sum decimal(23, 6)=0
    ,@sql varchar(1000)=''


while (exists(select FENTRYID from #t_research))
begin
    select top 1 @research_id=FENTRYID,@research_name=FDATAVALUE from #t_research
    delete from #t_research where FENTRYID=@research_id

    insert into #t_result(FID, FProjCostName) values(@row_seq, @research_name)
    set @row_sum=0
    set @col_seq=0
    while @col_seq <= @col_max
    begin
        set @value = 0
        set @value=(select sum(FAmt) from #t_data 
            where FF100005=@research_id 
            and FFLEX9 in (select fid from @t_cost_items where fseq=@col_seq))
        set @value = isnull(@value, 0)
        -- 累加一行中的每一列
        set @row_sum += @value
        -- 动态更新列
        set @sql='update #t_result set FItem' + convert(varchar(2), @col_seq)
            + '=' + convert(varchar(23), @value) 
            + ' where FID=' + convert(varchar(10), @row_seq)
        exec(@sql)
        
        set @col_seq += 1
    end
    -- print(@row_sum)
    update #t_result set FTotal=@row_sum where FID=@row_seq
    set @row_seq += 1
end

select * from #t_result
drop table #t_result
drop table #t_research
drop table #t_data
end

-- exec proc_czly_ResearchFeeDetailRpt @beginDt='2021-01-01', @endDt='2021-06-01', @orgNo='SS'
-- exec proc_czly_ResearchFeeDetailRpt @beginDt='#FBeginDate#', @endDt='#FEndDate#', @orgNo='#FOrgNo#'